<?php

class MarketController extends BaseController {



    public function getMkCl(){
      $a=$this->noFuck();

      $cl=Market::leftJoin('h_client', 'h_client.client_id', '=', 'h_market_research.client_id')
                ->leftJoin('h_client_contact', 'h_client_contact.client_contact_id', '=', 'h_market_research.client_contact_id')
                ->select($a);
                //->first();
      return $cl;          
      //print_r($cl);                

    }

    public function noFuck(){
      $a=array(
          "market_survey_no"  ,
          //"object_state" ,
          "type_rent" , "type_sale",
          "market_research_status",
          //"owner" ,"owner_mobile" ,"owner_phone" ,
          //"market_research_status",          
          //"principal" ,"principal_mobile" ,"principal_phone"  ,
          /*------------------------------------*/
          'h_client.name as c_name',
          'h_client.mobile as c_mobile',
          'h_client.home_phone as c_home_phone',
          'h_client.email as c_email',
          /*------------------------------------*/
          
          "market_research_status",          

          /*------------------------------------*/
          'h_client_contact.name as c_c_name' ,
          'h_client_contact.mobile as c_c_mobile' ,
          'h_client_contact.home_phone as c_c_home_phone' ,  
          'h_client_contact.email as c_c_email' ,  
          /*------------------------------------*/



          "case_name" ,
          "building_name" ,"object_usage" ,"object_styles" ,
          "administrative_area" ,"house_building" ,"area" ,"build_number" ,
          
          "sale_total" ,"sale_parking" ,"sale_housing_prices" ,
          "sale_housing_unit_price" ,"sale_parking_price" ,
          "sale_parking_unit_price" ,"sale_land_unit_price" ,
          
          "rent_total" ,
          //"rent_tax" ,
          "rent_parking" ,
          "rent_housing_rent" ,"rent_parking_space_rentals" ,
          "rent_rent_per_ping" ,"rent_monthly_rent_parking_spaces" ,

          "rent_deposit_month" ,
          "rent_deposit_money" ,
          "rent_min" ,
          "rent_max" ,
          "rent_lease_hike" ,
          "rent_rent_free_period_decoration" ,"rent_ancillary" ,
          "rent_notarization" ,

          "building_area" ,
          "main_building_area" ,
          "subsidiary_building_area" ,
          "postulate" ,
          "total_weight_shaped_area" ,
          "with_parking_number" ,
          
          "parking_area" ,
          "parking_area_number" ,
          "use_area" ,

          "postulate_than" ,
          "base_is_located1" ,
          "base_is_located2" ,
          "to_no" ,
          "base_area" ,

          "scope_of_rights1" ,
          "scope_of_rights2" ,
          "stakeholders_land_area" ,
          "present_value" ,

          "total_present_value1" ,
          "total_present_value2" ,
          "land_use_zoning" ,
          "volume_rate" ,

          "building_coverage_ratio" ,
          "monthly_management_fee" ,
          "management_fees_per_ping" ,
          "management" ,
          //"attached_configuration" ,
          
          "indoor_pattern_house" ,
          "indoor_pattern_hall" ,
          "indoor_pattern_guard" ,
          "indoor_pattern_suite" ,"windows" ,


          "household_door" ,
          "building_entrance" ,
          "households" ,"building_households" ,"total_towers" ,

          
          "total_households" ,


          "floor" ,
          "floor_plan1" ,
          "floor_plan2" ,
          "number_of_lifts1" ,
          "number_of_lifts2" ,          
          "facing_the_lane" ,

          "use_status" ,
          "lease_maturity" ,
          "lessee_by_industry" ,
          "floor_height" ,
          "building_exterior" ,
          "main_building" ,
          "building_completion_date" ,
          "year_of_construction" ,"annual_rent" ,

          
          "reported_rate" ,"construction_company" ,"construction_license_no" ,
          "look_mode" ,"key_management" ,"location_map1" ,
          "location_map2" ,"currency" ,

          
          "area_unit" ,"bank_set_total_amount" ,"loan_bank" ,"other_settings_amount" ,
          "bring_own_money" ,"estimated_loan" ,


          "environmental_features" ,"object_features" ,
          "public_facilities" ,"fire_and_security" ,"circle1" ,"circle2" ,

          "park1" ,"park2" ,
          "mrt_line1" ,"mrt1" ,"mrt_line2" ,"mrt2" ,


          "school_district_primary" ,
          "school_district_high" ,"school_district_senior" ,
          "spaces_where_floor" ,
          "parking_numbers" ,"parking_entrance_height" ,
          "public_parking_spaces1" ,
          "public_parking_spaces2" ,
          "separate_parking1" ,
          "separate_parking2" ,

          
          "car_park_management_fee" ,
          "total_management_fee_parking" ,
          "parking_type1" ,
          "parking_number1" ,
          "parking_price1" ,
          "parking_type2" ,
          "parking_number2" ,
          "parking_price2" ,

          
          "use_parking_number" ,
          "ballot" ,
          
          // "subsidiary_sun_area" ,
          // "subsidiary_flower_sun" ,"subsidiary_falt_sun" ,"subsidiary_terrace_flat" ,
          // "subsidiary_no_sun" ,"subsidiary_subsid_other" ,"pubilc_set_p_main" ,
          // "pubilc_set_p_big" ,"pubilc_set_p_small" ,
          // "main_build_area_isfloor" ,"main_build_area_main_area" ,"main_build_area_staircases" ,
          // "main_build_area_m_b_other" ,"inside_crid_troom" ,"inside_crid_inside_room" ,
          // "inside_crid_livingroom" ,
          // "inside_crid_restroom" ,
          // "inside_crid_bookroom" ,
          // "inside_crid_manageroom" ,
          // "inside_crid_closeroom" ,
          // "inside_crid_saveroom" ,
          // "inside_crid_otherroom" ,

          // "t_use_area_courtyard_area" ,
          // "t_use_area_basement" ,
          // "t_use_area_terrace" ,
          // "t_use_area_additional" ,

          "internet" ,
          "valuation_date" ,"valuation_amount" ,
          "appraisal_agencies" ,

          "object_case_type" ,"transaction_prices_near" ,"bcc" ,
          
          "status" ,"invalid_description" ,
          //"remark" ,
          //"front_man" ,
          //"head"  ,
          //"broker"
          "h_market_research.remark as m_remark" ,

          "h_market_research.front_man as m_front_man" ,
          "h_market_research.head as m_head"  ,
          "h_market_research.broker as m_broker",
          );
        return $a;

    }
   
    
    public function getMrkTitle($val){
     $title=array(   
      "market_survey_no"=>"市調編號",
      "market_research_status"=> "市調狀態",
      "type_rent"=>"租",
      "type_sale"=>"售",

      //"所有權人", "手機","電話",
      'c_name'=>"所有權人", 
      'c_mobile'=>"所有權人-手機",
      'c_home_phone'=>"所有權人-電話",
      'c_email'=>"所有權人-email",

      "market_research_status"=>"市調狀態",

      'c_c_name'=>"委托人",
      'c_c_mobile'=>"委托人-手機",
      'c_c_home_phone'=>"委托人-電話",
      'c_c_email'=>"委托人-email",
      //"委托人","手機","電話",
      "case_name"=>"案名",
      "building_name"=>"大樓名稱",
      "object_usage"=>"物件用途",
      "object_styles"=>"物件型式",
      "administrative_area"=>"建物門牌-行政區",
      "house_building" =>"建物門牌-地址",
      "area"=>"區域",
      "build_number"=>"建號",

      "sale_total"=>"售-總價",
      "sale_parking" =>"售-車位",
      "sale_housing_prices" =>"售-房屋售價",
      "sale_housing_unit_price"=>"售-房屋單價",
      "sale_parking_price" =>"售-車位售價",
      "sale_parking_unit_price"=>"售-車位單價",
      "sale_land_unit_price"=>"售-土地單價",


      "rent_total"=>"租-總價",
      "rent_parking" =>"租-車位",
      "rent_housing_rent"=>"租-房屋租金",
      "rent_parking_space_rentals"=>"租-車位租金",
      "rent_rent_per_ping"=>"租-每坪租金",
      "rent_monthly_rent_parking_spaces"=>"租-每月車位租金",


      "rent_deposit_month"=>"租-押金-月",
      "rent_deposit_money"=>"租-押金-萬",
      "rent_min"=>"租-租期最少",
      "rent_max"=>"租-租期最多",
      "rent_lease_hike"=>"租-租期調漲",
      "rent_rent_free_period_decoration"=>"租-免租裝潢期",
      "rent_ancillary"=>"租-附屬配備",
      "rent_notarization"=>"租-公證",


      "building_area" =>"建物面積",
      "main_building_area"=>"主建物面積",
      "subsidiary_building_area"=>"附屬建物面積",

      "postulate"=>"公設",
      "total_weight_shaped_area"=>"總權狀面積-坪",
      "with_parking_number"=>"總權狀面積-車位",
      "parking_area"=>"車位面積-坪",
      "parking_area_number"=>"車位面積-個",
      "use_area"=>"使用面積",
      
      "postulate_than"=>"公設比",
      "base_is_located1"=>"基地座落-段",
      "base_is_located2"=>"基地座落-小段",
      "to_no"=>"地號",
      "base_area"=>"基地面積",
      
      "scope_of_rights1"=>"權利范圍1",
      "scope_of_rights2"=>"范圍2",
      "stakeholders_land_area"=>"土地持份面積",
      "present_value"=>"公告現值",

      "total_present_value1"=>"總公告現值-萬",
      "total_present_value2"=>"總公告現值-年",
      "land_use_zoning"=>"土地使用分區",
      "volume_rate"=>"容積率",
      
      "building_coverage_ratio"=>"建蔽率",
      "monthly_management_fee"=>"每月管理費",
      "management_fees_per_ping"=>"每坪管理費",
      "management"=>"管理方式",

      "indoor_pattern_house"=>"室內格局-房",
      "indoor_pattern_hall"=>"室內格局-廳",
      "indoor_pattern_guard"=>"室內格局-衛",
      "indoor_pattern_suite"=>"室內格局-套房",
      "windows"=>"落地窗",


      "household_door"=>"該戶大門",
      "building_entrance"=>"本棟大門",
      "households"=>"當層戶數",
      "building_households"=>"該棟戶數",
      "total_towers"=>"總棟數",
      "total_households"=>"總戶數",

      "floor"=>"所在樓層",
      "floor_plan1"=>"樓層規劃-地上",
      "floor_plan2"=>"樓層規則-地下",
      "number_of_lifts1"=>"電梯數-電梯",
      "number_of_lifts2"=>"電梯數-貨梯",
      "facing_the_lane"=>"面臨路寬",


      "use_status"=>"使用狀況",
      "lease_maturity"=>"租賃到期日",
      "lessee_by_industry"=>"承租方行業別",
      "floor_height"=>"樓地板高度",
      "building_exterior"=>"建物外觀",
      "main_building"=>"主要建材",
      "building_completion_date"=>"建物完成日",
      "year_of_construction"=>"屋齡",

      "annual_rent"=>"年租金",

      "reported_rate"=>"投報率",
      "construction_company"=>"建設公司",
      "construction_license_no"=>"建築執照號",
      
      "look_mode"=>"帶看方式",
      "key_management"=>"鑰匙管理",
      "location_map1"=>"緯度",
      "location_map2"=>"經度",
      "currency"=>"幣別",

      "area_unit"=>"面積單位",
      "bank_set_total_amount" =>"銀行設定總金額",
      "loan_bank" =>"貸款銀行",
      "other_settings_amount"=>"其他設定金額",
      "bring_own_money"=>"自備款",
      "estimated_loan"=>"預估貸款",

      "environmental_features"=>"環境特色",
      "object_features"=>"物件特色",
      "public_facilities"=>"公共設施",
      "fire_and_security" =>"消防保全",
      "circle1"=>"商圈1",
      "circle2"=>"商圈2",
      "park1"=>"公園1",
      "park2"=>"公園2",
      "mrt_line1"=>"捷運線1",
      "mrt1"=>"捷運站1",
      "mrt_line2"=>"捷運線2",
      "mrt2"=>"捷運站2",

      "school_district_primary"=>"學區（國小）",
      "school_district_high"=>"學區（國中）",
      "school_district_senior"=>"學區（高中）",
      "spaces_where_floor"=>"車位所在樓層",
      "parking_numbers"=>"車位號碼",
      "parking_entrance_height"=>"車位入口高度",
      "public_parking_spaces1"=>"公設車位-坪",
      "public_parking_spaces2"=>"公設車位-個",
      "separate_parking1"=>"獨立權狀車位-坪",

      "separate_parking2"=>"獨立權狀車位-個",

      "car_park_management_fee"=>"車位管理費",
      "total_management_fee_parking"=>"車位總管理費",
      "parking_type1"=>"車位類型",
      "parking_number1"=>"類型個數",
      "parking_price1" =>"車位單價",
      "parking_type2"=>"車位類型1",
      "parking_number2"=>"類型個數1",
      "parking_price2"=>"車位單價1",

      "use_parking_number"=>"使用權車位數",
      "ballot"=>"抽籤-年",
      
      "internet" =>"上網",
      "valuation_date"=>"鑑價日期",

      "valuation_amount"=>"鑑價金額",
      "appraisal_agencies"=>"鑑價機構",


      "object_case_type"=>"物件案別",
      "transaction_prices_near"=>"附近成交行情",
      "bcc"=>"密件",
            
      "status"=>"狀態",
      "invalid_description"=>"無效說明",

      "m_remark"=>"備注",
       
      "m_front_man"=>"接待人",
      "m_head"=>"指派主管",
      //"指派日期",
      "m_broker"=>"指派經紀人");

      return isset($title[$val])?$title[$val]:'';

    }
    public function getMarket() {

        $a=$this->noFuck();
        // $mrk=Market::select($a);
        $cl=Market::leftJoin('h_client', 'h_client.client_id', '=', 'h_market_research.client_id')
                ->leftJoin('h_client_contact', 'h_client_contact.client_contact_id', '=', 'h_market_research.client_contact_id')
                ->select($a);

        return $cl;
    }

    public function getMarketById() {

        
        //$a=$this->noFuck();

        $mrk = $this->getMkCl();

        $hm=DB::table("h_savemarket")->get();

        foreach($hm as $hmv){
            
            $mrk->orwhere("market_research_id","=",$hmv->market_id);
            $mrk->orwhere("market_research_id","=",$hmv->market_id);
        }

        DB::table("h_savemarket")->truncate();
          
        return $mrk;
    }


 

     

     
    




    public function refData(){     

      $tbs=$query = DB::select('SHOW TABLES');

      $tables=array();           
      $data=array();

      foreach($tbs as $tb){

          //if($tb->Tables_in_house=='h_users'){
            $tables[]=$tb->Tables_in_house;


              $empty=DB::table($tb->Tables_in_house)
                          ->paginate(100)->toArray();
              $data[$tb->Tables_in_house] =$empty["data"];
          //}
      }

      return $data;
      //print_r($data);

    }



    public function mrk(){

       
      $mrk    =  $this->getMkCl();  
      $data=$this->excelData($mrk);  

      //print_r($data);

      Excel::create(date("ymd_his"), function($excel) use($data) {

            $excel->sheet('Sheetname', function($sheet) use($data) {

                $sheet->fromArray($data);
                $sheet->setAutoSize(true);
                $sheet->setFont(array(               
                    'size'       => '14',
                    'bold'       => true
                ));
            });

      })->export('xls');


    }


    function excelData($mrk){
      $refData=$this->refData();
      //$mrk    =  $mrk;      
      $mrk    =  $mrk->orderby("market_research_id","desc")->get()->toArray();
      //print_r($mrk);
      // $d = array();
      
      $_flg='Y';

      $icount=12;

      //$d[0]=$this->getMrkTitle();

      foreach ($mrk as $aa => $bb) {
          
          
          
           foreach ($bb as $a => $b) { 

                  if($_flg=='Y') {

                    $d[1][$a]=$this->getMrkTitle($a);

                  }else{

                  // echo $a .'=>'. $b;
                  // echo '<br>';
                    // if($b=='0' OR $b==0 OR empty($b)){
                    //   $d[$icount][$a] = ' ';
                    // }else{


                      if($a=="type_rent" OR $a=="type_sale" ){

                          if($a=="type_rent" AND $b==1 ){
                            $d[$icount][$a] = "租";
                          }elseif($a=="type_sale" AND $b==1){
                            $d[$icount][$a] = '售';
                          }else{
                            $d[$icount][$a] = '-';
                          }

                          /*"front_man" ,
                    "head" ,
                    "assigned_date" ,
                    "broker"*/
                      }elseif($a=='m_front_man'){
                            
                            foreach($refData['h_users'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->users_id){                      
                                  $d[$icount][$a] = $h_users->name;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='market_research_status'){
                            
                            // foreach($refData['h_object_state'] as $h_users){
                            //   if(!empty($b)){
                            //     if($b==$h_users->object_state_id){                      
                            //       $d[$icount][$a] = $h_users->value;
                            //     }
                            //   }else{
                            //     if($b==0 OR $b=='0' OR empty($b) ){
                            //       $d[$icount][$a] = ' ';
                            //     }else{
                                  $d[$icount][$a] = $b;
                            //     }

                            //   }  
                            // }

                      }elseif($a=='internet'){

                        if($b==1){      
                          $d[$icount][$a] = '是'; 
                        }else{
                          $d[$icount][$a] = '否'; 
                        }

                      }elseif($a=='m_head'){
                            
                            foreach($refData['h_users'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->users_id){                      
                                  $d[$icount][$a] = $h_users->name;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='m_broker'){
                            
                            foreach($refData['h_users'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->users_id){                      
                                  $d[$icount][$a] = $h_users->name;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='management'){
                            
                            foreach($refData['h_management'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->management_id){                      
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='building_exterior'){
                            
                            foreach($refData['h_building_exterior'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->building_exterior_id){                      
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='main_building'){
                            
                            foreach($refData['h_main_building'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->main_building_id){                      
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='look_mode'){
                            
                            foreach($refData['h_look_mode'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->look_mode_id){                      
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='key_management'){
                            
                            foreach($refData['h_management'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->management_id){
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }
                            //rent_notarization  
                      }elseif($a=='rent_notarization'){
                            
                            if($b){
                                $d[$icount][$a] = '有';
                            }else{
                              $d[$icount][$a] = '-';

                            }
                            
                      }elseif($a=='mrt_line1'){
                            
                            foreach($refData['h_mrt_line'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->mrt_line_id){
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='mrt_line2'){
                            
                            foreach($refData['h_mrt_line'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->mrt_line_id){
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='mrt1'){
                            
                            foreach($refData['h_mrt'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->mrt_id){
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='mrt2'){
                            
                            foreach($refData['h_mrt'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->mrt_id){
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='object_state'){
                            
                            foreach($refData['h_object_state'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->object_state_id){                      
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='parking_type1'){
                            
                            foreach($refData['h_parking_type'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->parking_type_id){                      
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }
                            
                      }elseif($a=='market_survey_no'){                                                        
                                $d[$icount][$a] = $b;                            
                      }elseif($a=='parking_type2'){
                            
                            foreach($refData['h_parking_type'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->parking_type_id){                      
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='use_status'){
                            
                            foreach($refData['h_use_status'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->use_status_id){                      
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='land_use_zoning'){
                            
                            foreach($refData['h_land_use_zoning'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->land_use_zoning_id){
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='area_unit'){
                            
                            foreach($refData['h_area_unit'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->area_unit_id){
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }elseif($a=='currency'){
                            
                            foreach($refData['h_currency'] as $h_users){
                              if(!empty($b)){
                                if($b==$h_users->currency_id){                      
                                  $d[$icount][$a] = $h_users->value;
                                }
                              }else{
                                $d[$icount][$a] = $b;
                              }  
                            }

                      }
                           elseif($a=='object_case_type'){
                             
                            $_vb      = explode(',' , $b);
                            $_vb_str  = '';  
 
                              $__pp='';
                                foreach($_vb as $ee=>$_saaar){
 
                                      if($_saaar==1){
                                         $__pp.= "中古屋/";
                                      }elseif($_saaar==2){
                                         $__pp .= "預售/";
                                      }elseif($_saaar==3){
                                        $__pp.= "標售/";
                                      }


                            }
                             $d[$icount][$a] =$__pp;

                            if(!empty($__pp)){
                                                
                                $d[$icount][$a] =$__pp;
                             
                            }else{
                              $d[$icount][$a] = $b;
                            }  

                      }
                      elseif($a=='status'){
                                                                                      
                                if($b==1){                      
                                  $d[$icount][$a] = '有效';
                                }else{
                                  $d[$icount][$a] = $b;
                                }  
                            

                      }elseif($a=='remark'){
                                                        
                          $d[$icount][$a] = (String)" ".$b." ";                            

                      }elseif($a=='c_mobile'){
                                                        
                          $d[$icount][$a] = (String)" ".$b." ";                            

                      }elseif($a=='c_home_phone'){
                                                        
                          $d[$icount][$a] = (String)" ".$b." ";                            

                      }elseif($a=='c_c_mobile'){
                                                        
                          $d[$icount][$a] = (String)" ".$b." ";                            

                      }elseif($a=='c_c_home_phone'){
                                                        
                          $d[$icount][$a] = (String)" ".$b." ";                            

                      }else{
                          if(empty($b) OR $b=='0'){
                            $d[$icount][$a] = '-';
                          }else{
                            $d[$icount][$a] = $b;
                          }
                          
                      }
                  //}

                }

          }

          $_flg='N';
          $icount++;
      }

      return $d;
      
    }

    public function allDb(){
      
      $tbs=$query = DB::select('SHOW TABLES');
      $tables=array();           
      $data=array();

      foreach($tbs as $tb){

          if($tb->Tables_in_house=='h_users'){
            $tables[]=$tb->Tables_in_house;


              $empty=DB::table($tb->Tables_in_house)
                          ->paginate(100)->toArray();
              $data[$tb->Tables_in_house] =$empty["data"];
          }
      }

       


    }


    public function postMarketId(){
      
      $_pa=Input::all();
      //print_r($_pa['id']);
      $_ids=explode(',', $_pa['id']);
      
      foreach ($_ids as $key => $id) {

           DB::table("h_savemarket")->insert( array('market_id'=>$id) );
      
      }
       
    }


    public function getMarketIdExcel(){

      $mrk    =  $this->getMarketById();  
      $data=$this->excelData($mrk);  

      //print_r($data);

      Excel::create(date("ymd_his"), function($excel) use($data) {

            $excel->sheet('Sheetname', function($sheet) use($data) {

                $sheet->fromArray($data);
                $sheet->setAutoSize(true);
                $sheet->setFont(array(               
                    'size'       => '14',
                    'bold'       => true
                ));
            });

      })->export('xls');

    }

}
    